package datamanagers;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.HashMap;
import java.util.Map;

public class Voti {
	/** recupera tutti gli utenti che l'userID ha votato  e li mette in una mappa <profileID,rating>  
	 * @throws DataManagerException */
	public Map<Float,Integer>recuperoVotiDati(float userID) throws DataManagerException{
		Map <Float,Integer>votiDati =new HashMap<Float, Integer>();
		DatasourceMySql dataSource = new DatasourceMySql();
		Connection connection = null;
		PreparedStatement statement = null;
		ResultSet result = null;
		try {			
			connection = dataSource.getConnection();
			// select * from sii2012.tbl_giudizi where userID=12331;
			String retrieve = "select * from sii_challenge2012.tbl_ratings where userID=?;";
			statement = connection.prepareStatement(retrieve);
			statement.setFloat(1,userID);
			result = statement.executeQuery();

			while (result.next()) {
				Float profileID = result.getFloat("profileID");
				Integer rating = result.getInt("rating");
				votiDati.put(profileID, rating);
			}

		}
		catch (SQLException e) {
			throw new DataManagerException(e);
		}
		finally {
			try {
				if (result != null)
					result.close();
				if (statement != null) 
					statement.close();
				if (connection!= null)
					connection.close();
			} catch (SQLException e) {
				throw new DataManagerException(e);
			}
		}

		return votiDati;

	}
/** ritorna la media dei voti assegnati dall'userID */
	public Integer mediaAssegnata (float userID)throws DataManagerException{

		//	select avg(rating) from sii2012.tbl_giudizi where userID=12331;
		Integer votiMedia=0;
		DatasourceMySql dataSource = new DatasourceMySql();
		Connection connection = null;
		PreparedStatement statement = null;
		ResultSet result = null;
		try {			
			connection = dataSource.getConnection();
			// select * from sii2012.tbl_giudizi where userID=12331;
			String retrieve = "select avg(rating) as media from sii_challenge20122.tbl_ratings where userID=?;";
			statement = connection.prepareStatement(retrieve);
			statement.setFloat(1,userID);
			result = statement.executeQuery();

			if (result.next()) {
			votiMedia = result.getInt("media");
			}
		}
		catch (SQLException e) {
			throw new DataManagerException(e.getMessage());
		}
		finally {
			try {
				if (result != null)
					result.close();
				if (statement != null) 
					statement.close();
				if (connection!= null)
					connection.close();
			} catch (SQLException e) {
				throw new DataManagerException(e);
			}
		}

		return votiMedia;

	}
/** recupera tutti i voti dati al ProfileID */
	public Map<Float,Integer>recuperoUserIDVotatoProfileID(float profileID)throws DataManagerException{
		Map <Float,Integer>votiDati = new HashMap<Float, Integer>();
		DatasourceMySql dataSource = new DatasourceMySql();
		Connection connection = null;
		PreparedStatement statement = null;
		ResultSet result = null;
		try {			
			connection = dataSource.getConnection();
			// select * from sii2012.tbl_giudizi where userID=12331;
			String retrieve = "select * from sii_challenge2012.tbl_ratings where profileID=?;";
			statement = connection.prepareStatement(retrieve);
			statement.setFloat(1,profileID);
			result = statement.executeQuery();

			while (result.next()) {
				Float userID = result.getFloat("userID");
				Integer rating = result.getInt("rating");
				votiDati.put(userID, rating);
			}

		}
		catch (SQLException e) {
			throw new DataManagerException(e);
		}
		finally {
			try {
				if (result != null)
					result.close();
				if (statement != null) 
					statement.close();
				if (connection!= null)
					connection.close();
			} catch (SQLException e) {
				throw new DataManagerException(e.getMessage());
			}
		}

		return votiDati;

	 
	}
	
	public Integer recuperoValoreUserIDProfileID (float profileID,float userID)throws DataManagerException{
		
		Integer rating =0;
		DatasourceMySql dataSource = new DatasourceMySql();
		Connection connection = null;
		PreparedStatement statement = null;
		ResultSet result = null;
		try {			
			connection = dataSource.getConnection();
			// select * from sii2012.tbl_giudizi where userID=12331;
			String retrieve = "select rating from sii_challenge2012.tbl_ratings where profileID=? and userID=?;";
			statement = connection.prepareStatement(retrieve);
			statement.setFloat(1,profileID);
			statement.setFloat(2, userID);
			result = statement.executeQuery();

			if (result.next()) {
				rating = result.getInt("rating");
				 
			}

		}
		catch (SQLException e) {
			throw new DataManagerException(e);
		}
		finally {
			try {
				if (result != null)
					result.close();
				if (statement != null) 
					statement.close();
				if (connection!= null)
					connection.close();
			} catch (SQLException e) {
				throw new DataManagerException(e);
			}
		}

		return rating;

	 
	}
	
	
	  
}
